from PyQt5.QtWidgets import QApplication, QMainWindow, QWidget, QTableWidgetItem, QMessageBox, QLineEdit
from PyQt5.uic import loadUiType
import pymysql
from PyQt5 import QtCore
######################################## 加载UI文件 ######################################################################
ui1, _ = loadUiType('qxdl.ui')
ui2, _ = loadUiType('qxzc.ui')
ui3, _ = loadUiType('qxmain.ui')
########################################## 登录界面 ######################################################################
class DlApp(QWidget,ui1):
    def __init__(self):                # 构造函数方法
        super(DlApp, self).__init__()  # 调用父类构造
        self.setupUi(self)             # 初始化操作
        self.button()                  # 调用定义的按钮函数

# 自定义按钮连接槽函数
    def button(self):
        self.pushButton_zc.clicked.connect(self.zhuce)   # 注册按钮与跳转到注册界面的函数相连接
        self.pushButton_dl.clicked.connect(self.denglu)  # 登录按钮与登录到主界面的函数连接
# 从登录跳转到注册页面
    def zhuce(self):
        self.a=ZcApp()   # 把注册界面zcapp()赋值给a
        self.close()     # 当前界面关闭
        self.a.show()    # 注册界面打开
# 定义登录功能，从登录界面跳转到主界面
    def denglu(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接数据库
        cur = db.cursor()          # 定义游标
        sql = "select * from user where name=%s and pwd=%s"  # sql语句
        a = self.lineEdit_name.text()
        b = self.lineEdit_pwd.text()
        cur.execute(sql, (a, b))   # 执行sql语句
        data = cur.fetchone()      # 取其中一条数据
        if data:                   # 如果存在数据，则执行以下功能
            self.a3 = MainApp()    # 把mainAPP窗口赋值为a3
            self.close()           # 当前窗口关闭
            self.a3.show()         # 主窗口打开

############################################# 注册界面 ###################################################################
class ZcApp(QWidget,ui2):
    def __init__(self):                # 构造函数方法（魔术方法）
        super(ZcApp,self).__init__()   # 调用父类构造
        self.setupUi(self)             # 初始化操作
        self.button()                  # 调用定义的按钮函数

    def button(self):
        self.pushButton_fanhui.clicked.connect(self.fanhui)  # 从注册界面返回登录界面
        self.zhuceuser.clicked.connect(self.zhuceusers)      # 注册按钮与注册用户的函数相连接

    def fanhui(self):      # 定义返回登录界面的函数
        self.a1=DlApp()
        self.close()
        self.a1.show()

    def zhuceusers(self):   # 定义注册用户的函数
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接数据库
        cur = db.cursor()           # 创建游标
        sql = "insert into user(name,pwd) values(%s,%s)"  # sql语句
        username = self.user_name1.text()                 # 实现从指定界面输入框获取指定输入信息
        userpwd  = self.user_pwd2.text()
        userpwd1 = self.user_pwd1.text()
        if userpwd == userpwd1:                   # 如果密码和确认密码相同，则执行以下代码
            cur.execute(sql, (username,userpwd))  # 执行sql语句
            db.commit()                           # 提交
            cur.close()
            db.close()                            # 关闭数据库
            QMessageBox.information(self, "提醒", "您已成功注册账号!", QMessageBox.Yes, QMessageBox.Yes)

        else:
            QMessageBox.warning(self, "警告", "出错了！，请重新输入", QMessageBox.Yes, QMessageBox.Yes)

################################################ 主界面 ##################################################################
class MainApp(QMainWindow,ui3):
    def __init__(self):                  # 构造函数方法（魔术方法）
        super(MainApp,self).__init__()   # 调用父类构造
        self.setupUi(self)
        self.button()
        self.ui_change()
        self.show_qxinfo()   # 显示球鞋信息
        self.show_xsinfo()   # 显示销售信息


    def button(self):
        self.pushButton_fh.clicked.connect(self.fanhui)            # 返回
        self.pushButton60.clicked.connect(self.open_qx_info_tab)   # 这三个是连接选项卡
        self.pushButton61.clicked.connect(self.open_xs_tab)
        self.pushButton62.clicked.connect(self.open_user_info_tab)

        self.pushButtondeng.clicked.connect(self.user_login)   # 这三个是用户管理里面的增删改查按钮连接
        self.pushButtonxiugai.clicked.connect(self.xg_user)
        self.pushButton_delete_user.clicked.connect(self.delete_user_info)

        self.addqx_info.clicked.connect(self.add_qx_info)      # 这个是球鞋管理里面的增删改查按钮连接
        self.pushButton_select.clicked.connect(self.select_qx_info)
        self.pushButton_select_2.clicked.connect(self.qxinfo_detele_xiugai)
        self.pushButtonxg1.clicked.connect(self.xiugai_qx_info)
        self.pushButton_shangchu.clicked.connect(self.delete_qx_info)

        self.add_xs_info.clicked.connect(self.add_xs)          # 这个是销售管理里面的增删改查按钮连接
        self.pushButton_select_3.clicked.connect(self.select_xs_info)
        self.pushButton_select_4.clicked.connect(self.xsinfo_detele_xiugai)
        self.pushButtonxg1_2.clicked.connect(self.xiugai_xs_info)
        self.pushButton_shangchu_2.clicked.connect(self.delete_xs_info)


    def ui_change(self):
        self.tabWidget.tabBar().setVisible(False)  # 隐藏tab 把最上面那个容器隐藏

    def fanhui(self):
        self.a4 = DlApp()  # 实例变量问题，必须把它作为自己的一个实例变量才可以，不然是属于这样一个方法里面的临时变量，当这个方法结束以后这个变量就销毁了。
        self.close()
        self.a4.show()
# 选项卡的联动
    def open_qx_info_tab(self):
        self.tabWidget.setCurrentIndex(0)    # 索引为0
    def open_xs_tab(self):
        self.tabWidget.setCurrentIndex(1)    # 索引为1
    def open_user_info_tab(self):
        self.tabWidget.setCurrentIndex(2)    # 索引为2

############################################# 球鞋管理 ###################################################################
# 增加球鞋信息
    def add_qx_info(self):
        # 数据库操作流程
        # 1.获取连接
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into qx_info(id,name,size,jiage,scd) values(%s,%s,%s,%s,%s)"
        a = self.add_qx_id.text()   # 实现从指定界面输入框获取指定输入信息
        b = self.qx_name.text()
        c = self.lineEdit.text()
        d = self.qx_sjia.text()
        e = self.qx_scd.text()
        # 4.执行语句
        cur.execute(sql, (a,b,c,d,e))
        # 5.提交
        db.commit()
        # 6.关闭资源
        db.close()
        cur.close()
        self.show_qxinfo()
# 显示球鞋信息
    def show_qxinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from qx_info"
        cur.execute(sql)
        data = cur.fetchall()    # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_yxinfo.setRowCount(0)
            self.tableWidget_yxinfo.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_yxinfo.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_yxinfo.rowCount()
                self.tableWidget_yxinfo.insertRow(row_position)
# 查询球鞋信息
    def select_qx_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from qx_info where id=%s"
        yx_id = self.yxid.text()
        cur.execute(sql, (yx_id))
        data = cur.fetchone()
        if data:
            self.add_qx_id_2.setText(str(data[0]))
            self.qx_name_2.setText(data[1])
            self.lineEdit_2.setText(str(data[2]))
            self.qx_sjia_2.setText(str(data[3]))
            self.qx_scd_2.setText(data[4])

# 查询球鞋信息用来修改和删除
    def qxinfo_detele_xiugai(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from qx_info where id=%s"
        id = self.qxid_2.text()

        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_14.setEnabled(True)
            self.add_qx_id_5.setText(str(data[0]))
            self.qx_name_5.setText(data[1])
            self.lineEdit_5.setText(str(data[2]))
            self.qx_sjia_5.setText(str(data[3]))
            self.qx_scd_5.setText(data[4])
# 修改球鞋信息
    def xiugai_qx_info(self):
        id = self.qxid_2.text()
        a = self.add_qx_id_5.text()
        b = self.qx_name_5.text()
        c = self.lineEdit_5.text()
        d = self.qx_sjia_5.text()
        e = self.qx_scd_5.text()

        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "update qx_info set id=%s,name=%s,size=%s,jiage=%s,scd=%s where id=%s"
        cur.execute(sql, (a,b,c,d,e,id))
        db.commit()
        db.close()
        cur.close()
        self.show_qxinfo()   # 同步到显示里面
# 删除球鞋信息
    def delete_qx_info(self):
        id = self.qxid_2.text()
        warning = QMessageBox.warning(self, "删除球鞋信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
            cur = db.cursor()
            sql = "delete from qx_info where id=%s"
            cur.execute(sql, (id))
            db.commit()
            db.close()
            cur.close()
            self.show_qxinfo()   # 同步到显示里面
################################################ 销售管理 ################################################################
# 增加
    def add_xs(self):
        # 数据库操作流程
        # 1.获取连接
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        # 2.获取游标
        cur = db.cursor()
        # 3.SQL语句
        sql = "insert into xs_info(id,qx_id,jiage,number,dz,lxfs) values(%s,%s,%s,%s,%s,%s)"
        a = self.add_xs_id_4.text()  # 实现从指定界面输入框获取指定输入信息
        b = self.add_qx_id_3.text()
        c = self.lineEdit_3.text()
        d = self.qx_sjia_3.text()
        e = self.qx_scd_3.text()
        f = self.lineEdit_7.text()
        # 4.执行语句
        cur.execute(sql, (a, b, c, d,e,f))
        # 5.提交
        db.commit()
        # 6.关闭资源
        db.close()
        cur.close()
        self.show_xsinfo()
# 显示
    def show_xsinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from xs_info"
        cur.execute(sql)
        data = cur.fetchall()  # 获取查询到的数据，是以二维元组的形式存储的，所以读取需要使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_xsinfo_2.setRowCount(0)
            self.tableWidget_xsinfo_2.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_xsinfo_2.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_xsinfo_2.rowCount()
                self.tableWidget_xsinfo_2.insertRow(row_position)
# 查询
    def select_xs_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from xs_info where id=%s"
        pf_id = self.xs_id_3.text()
        cur.execute(sql, (pf_id))
        data = cur.fetchone()
        if data:
            self.add_qx_id_7.setText(str(data[0]))
            self.add_qx_id_6.setText(str(data[1]))
            self.lineEdit_4.setText(str(data[2]))
            self.qx_sjia_4.setText(str(data[3]))
            self.qx_scd_4.setText(str(data[3]))
            self.lineEdit_8.setText(str(data[3]))

# 查询用来方便修改和删除
    def xsinfo_detele_xiugai(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from xs_info where id=%s"
        id = self.xsid_4.text()

        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_17.setEnabled(True)
            self.add_qx_id_9.setText(str(data[0]))
            self.add_qx_id_8.setText(str(data[1]))
            self.lineEdit_6.setText(str(data[2]))
            self.qx_sjia_6.setText(str(data[3]))
            self.qx_scd_6.setText(str(data[3]))
            self.lineEdit_9.setText(str(data[3]))
# 修改
    def xiugai_xs_info(self):
        id = self.xsid_4.text()
        a = self.add_qx_id_9.text()
        b = self.add_qx_id_8.text()
        c = self.lineEdit_6.text()
        d = self.qx_sjia_6.text()
        e = self.qx_scd_6.text()
        f = self.lineEdit_9.text()

        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "update xs_info set id=%s,qx_id=%s,jiage=%s,number=%s,dz=%s,lxfs=%s where id=%s"
        cur.execute(sql, (a, b, c, d,e,f, id))
        db.commit()
        db.close()
        cur.close()
        self.show_xsinfo()
# 删除
    def delete_xs_info(self):
        id = self.xsid_4.text()
        warning = QMessageBox.warning(self, "删除销售信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
            cur = db.cursor()
            sql = "delete from xs_info where id=%s"
            cur.execute(sql, (id))
            db.commit()
            db.close()
            cur.close()
            self.show_xsinfo()


########################################### 账号信息管理 ##################################################################
# 先登录
    def user_login(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "select * from user where name=%s and pwd=%s"
        a = self.user_name.text()
        b = self.user_pwd.text()
        cur.execute(sql,(a,b))
        data = cur.fetchone()
        if data:
            self.groupBox1.setEnabled(True)
            self.user_name1.setText(data[1])
            self.user_pwd1.setText(data[2])
# 修改
    def xg_user(self):
        a2 = self.user_name.text()
        b2 = self.user_name1.text()
        c2 = self.user_pwd1.text()
        d2 = self.user_pwd2.text()
        if c2 == d2:
            db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
            cur = db.cursor()
            sql = "update user set name=%s,pwd=%s where name=%s"
            cur.execute(sql, (b2,c2,a2))
            db.commit()
            db.close()
            cur.close()
# 注销  删除
    def delete_user_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="88888888", database="dbqx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()  # 游标
        user_name = self.user_name_4.text()
        user_pwd = self.editor_pwd_4.text()
        sql = "select * from user where name=%s and pwd=%s"
        cur.execute(sql,(user_name,user_pwd))
        date = cur.fetchone()
        if date:
            sql2 = "delete from user where name=%s"
            warning = QMessageBox.warning(self, "删除账户信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
            if warning == QMessageBox.Yes:
                cur.execute(sql2,(user_name))
                db.commit()
                db.close()
                cur.close()

####################################### main函数 ########################################################################
def main():
    app = QApplication([])     # 创建应用程序（无参数的） 创建一个QApplication，也就是你要开发的软件app
    window = DlApp()           # 创建主窗口  创建一个MainAPP，用来装载你需要的各种组件、控件  即创建对象
    window.show()              # 显示窗口 执行QMainWindow的show()方法，显示这个QMainWindow
    app.exec_()                # 使用exit()或者点击关闭按钮退出QApplication

if __name__ == '__main__':

    main()